﻿#include "musicinfo.h"
#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>
#include <QSqlError>
#include <QSqlRecord>

bool createListTable(QString& tableName)
{
    bool retCode = true;
       // 用于执行sql语句的对象
       QSqlQuery sqlQuery("myMusicPlayerDB");
       // 构建创建数据库的sql语句字符串
       QString createSql = QString("CREATE TABLE %1 (listIndex INTEGER ,MusicID INTEGER, MusicName TEXT, SingerName TEXT, album TEXT, songDuration FLOAT, MusicUrl TEXT PRIMARY KEY , MusicPic TEXT, bLoved INTEGER)").arg(tableName);
/*方式一   sqlQuery.exec(SQL请求)*/
//       bool success = sqlQuery.exec(createSql);
//       // 执行sql语句
//       if(!success)
//       {
//           qDebug() << "Error: Fail to create table. " << sqlQuery.lastError();
//             retCode = false;
//       }
//       else
//       {
//           qDebug() << "Table created!";
//       }
       /*方式二：query.prepare(SQL请求) + [bindValue(请求的参数)] + exec()*/
       sqlQuery.prepare(createSql);
       if(!sqlQuery.exec())
       {
           qDebug() << "Error: Fail to create table. " << sqlQuery.lastError();
           retCode = false;
       }
       else
       {
           qDebug() << "Table created!"<<retCode <<__FUNCTION__<<__LINE__;
       }

    sqlQuery.finish(); /* 关闭表格，下次使用前再开启即可，避免多次修改报错 */
    return retCode;
}

int getRcdCount(QString &tableName)
{
    int recordCount = -1;
    QSqlQuery sqlQuery;
    QString createSql = QString("SELECT COUNT(*) FROM %1").arg(tableName);
    bool bSuccess = sqlQuery.exec(createSql);
    qDebug() << createSql << bSuccess;
    if(!bSuccess)
    {
        qDebug()<<sqlQuery.lastError();
        return 0;
    }
    else
    {
        // 获取结果
        if (sqlQuery.next()) {
            QVariant count = sqlQuery.value(0);
            // 如果count是一个整数，可以直接转换为int
            recordCount = count.toInt();
            // 使用recordCount变量，它包含表中的记录数
            qDebug() << QString("%1 table has %2 recoeds").arg(tableName).arg(recordCount);
        }
        return recordCount;

    }
}
// 查询全部数据
bool queryTable(QString &tableName,QList<MusicInfo*> *pMusicList)
{
    QSqlQuery sqlQuery;
    QString createSql = QString("SELECT * FROM %1").arg(tableName);
    bool bSuccess = sqlQuery.exec(createSql);
    qDebug()<<createSql << bSuccess;
   if(!bSuccess)
   {
       qDebug() << "Error: Fail to query table. " << sqlQuery.lastError() << __FUNCTION__ <<__LINE__;
       return false;
   }
   else
   {
       while(sqlQuery.next())
       {

           MusicInfo  *pMusicInfo = new MusicInfo(\
                       sqlQuery.value(0).toInt(),\
                       sqlQuery.value(1).toInt(),\
                       sqlQuery.value(2).toString(),\
                       sqlQuery.value(3).toString(),\
                       sqlQuery.value(4).toString(),\
                       sqlQuery.value(5).toFloat(),\
                       sqlQuery.value(6).toString(),\
                       sqlQuery.value(7).toString(),\
                       sqlQuery.value(8).toInt());

           qDebug()<<QString("listIndex:%1 MusicID:%2 MusicName:%3 SingerName:%4 album:%5 songDuration:%6  MusicUrl:%7 MusicPic:%8 bLoved:%9")
                     .arg(sqlQuery.value(0).toInt()).arg(sqlQuery.value(1).toInt()).arg(sqlQuery.value(2).toString()).arg(sqlQuery.value(3).toString())
                     .arg(sqlQuery.value(4).toString()).arg(sqlQuery.value(5).toFloat()).arg(sqlQuery.value(6).toString())
                     .arg(sqlQuery.value(7).toString()).arg(sqlQuery.value(8).toInt());

          pMusicList->append(pMusicInfo);

       }
       return true;
   }
}

// 查询单条数据
MusicInfo* queryTableByindex(QString &tableName, int index)
{
    MusicInfo *pMusicInfo;
    QSqlQuery sqlQuery;
    //QString createSql = QString("SELECT * FROM %1 WHERE rowid = %2").arg(tableName).arg(QString::number(index));
    QString createSql = QString("SELECT * FROM %1 WHERE listIndex = %2").arg(tableName).arg(QString::number(index));
    bool bSuccess = sqlQuery.exec(createSql);
    qDebug()<<createSql << bSuccess;
   if(!bSuccess)
   {
       qDebug() << "Error: Fail to query table. " << sqlQuery.lastError() << __FUNCTION__ <<__LINE__;
       return nullptr;
   }
   else
   {
       while(sqlQuery.next())
       {

           pMusicInfo = new MusicInfo(\
                       sqlQuery.value(0).toInt(),\
                       sqlQuery.value(1).toInt(),\
                       sqlQuery.value(2).toString(),\
                       sqlQuery.value(3).toString(),\
                       sqlQuery.value(4).toString(),\
                       sqlQuery.value(5).toFloat(),\
                       sqlQuery.value(6).toString(),\
                       sqlQuery.value(7).toString(),\
                       sqlQuery.value(8).toInt());

           qDebug()<<QString("listIndex:%1 MusicID:%2 MusicName:%3 SingerName:%4 album:%5 songDuration:%6  MusicUrl:%7 MusicPic:%8 bLoved:%9")
                     .arg(sqlQuery.value(0).toInt()).arg(sqlQuery.value(1).toInt()).arg(sqlQuery.value(2).toString()).arg(sqlQuery.value(3).toString())
                     .arg(sqlQuery.value(4).toString()).arg(sqlQuery.value(5).toFloat()).arg(sqlQuery.value(6).toString())
                     .arg(sqlQuery.value(7).toString()).arg(sqlQuery.value(8).toInt());
           qDebug()<<"pMusicInfo=" << pMusicInfo <<__FUNCTION__<<__LINE__;

       }
       return pMusicInfo;
   }
}
bool UpdateData(QString &tableName, MusicInfo *pSingleDB)
{
    QSqlQuery sqlQuery;
    QString createSql = QString("UPDATE %1 SET MusicID=:MusicID,MusicName=:MusicName,SingerName=:SingerName,album=:album,songDuration=:songDurantion,MusicUrl=:MusicUrl, MusicPic=:MusicPic WHERE listIndex=:listIndex").arg(tableName);
    sqlQuery.prepare(createSql);
   // qDebug()<<"listIndex="<< pSingleDB->getIListIndex();
    sqlQuery.bindValue(":listIndex", pSingleDB->getIListIndex());
    //qDebug()<<"MusicID=" << pSingleDB->getI_MusicID();
    sqlQuery.bindValue(":MusicID", pSingleDB->getI_MusicID());
   // qDebug() << "MusicName=" << pSingleDB->getStrMusicName();
    sqlQuery.bindValue(":MusicName", pSingleDB->getStrMusicName());
    //qDebug() << "SingerName=" << pSingleDB->getStrSingerName();
    sqlQuery.bindValue(":SingerName", pSingleDB->getStrSingerName());
   // qDebug() << "album=" << pSingleDB->getAlbum();
    sqlQuery.bindValue(":album", pSingleDB->getAlbum());
    qDebug() << "songDuration=" << pSingleDB->getSongDuration();
    sqlQuery.bindValue(":songDuration",pSingleDB->getSongDuration());
    //qDebug() << "MusicUrl=" << pSingleDB->getMusicUrl();
    sqlQuery.bindValue(":MusicUrl", pSingleDB->getMusicUrl());
    //qDebug() << "MusicPic=" << pSingleDB->getMusicPic();
    sqlQuery.bindValue(":MusicPic",pSingleDB->getMusicPic());
    qDebug() << "bLoved=" << pSingleDB->getBLoved()<< __FUNCTION__<<__LINE__;
    sqlQuery.bindValue(":bLoved", pSingleDB->getBLoved());


    if(!sqlQuery.exec())
    {
        qDebug() << sqlQuery.lastError()<<__FUNCTION__<<__LINE__;
        return false;
    }
    else
    {
        qDebug() << "updated data success!"<<__FUNCTION__<<__LINE__;
        return true;
    }
}
//按行号更新整行数据
bool UpdateRowData(QString &tableName, MusicInfo *pSingleDB, int iRowID)
{
    QSqlQuery sqlQuery;
    QString createSql = QString("UPDATE %1 SET listIndex=:listIndex,MusicID=:MusicID,MusicName=:MusicName,SingerName=:SingerName,album=:album,songDuration=:songDurantion,MusicUrl=:MusicUrl, MusicPic=:MusicPic WHERE rowid =:Rid").arg(tableName);
    sqlQuery.prepare(createSql);
    sqlQuery.addBindValue(pSingleDB->getIListIndex());
    sqlQuery.addBindValue(pSingleDB->getI_MusicID());
    sqlQuery.addBindValue(pSingleDB->getStrMusicName());
    sqlQuery.addBindValue(pSingleDB->getStrSingerName());
    sqlQuery.addBindValue(pSingleDB->getAlbum());
    sqlQuery.addBindValue(pSingleDB->getSongDuration());
    sqlQuery.addBindValue(pSingleDB->getMusicUrl());
    sqlQuery.addBindValue(pSingleDB->getMusicPic());
    sqlQuery.addBindValue(pSingleDB->getBLoved());
    sqlQuery.bindValue(":Rid",iRowID);
    if(!sqlQuery.exec())
    {
        qDebug() << sqlQuery.lastError()<<__FUNCTION__<<__LINE__;
        return false;
    }
    else
    {
        qDebug() << "updated data success!"<<__FUNCTION__<<__LINE__;
        return true;
    }
}
//按主键更新某字段数据
bool UpdateIntData(QString &tableName, QString columnName, int newColValue,int keyValue)
{
    QSqlQuery sqlQuery;
    QString createSql = QString("UPDATE %1 SET %2 = %3 WHERE listIndex=%4").arg(tableName).arg(columnName).arg(newColValue).arg(keyValue);
    bool bSuccess = sqlQuery.exec(createSql);//sqlQuery.prepare("UPDATE student SET name=?,age=? WHERE id=?");
    qDebug()<<createSql << bSuccess <<__FUNCTION__<<__LINE__;
    if(!bSuccess)
    {
        qDebug() << sqlQuery.lastError();
        return false;
    }
    else
    {
        qDebug() << "updated data success!";
        return true;
    }
}

int getlovedRcdCount(QString &tableName)
{
    int recordCount = -1;
    QSqlQuery sqlQuery;
    QString createSql = QString("SELECT COUNT(*) FROM %1 WHERE bLoved = true").arg(tableName);
    bool bSuccess = sqlQuery.exec(createSql);
    qDebug()<<createSql << bSuccess <<__FUNCTION__<<__LINE__;
    if(!bSuccess)
    {
        qDebug()<<sqlQuery.lastError();
        return 0;
    }
    else
    {
        // 获取结果
        if (sqlQuery.next()) {
            QVariant count = sqlQuery.value(0);
            // 如果count是一个整数，可以直接转换为int
            recordCount = count.toInt();
            // 使用recordCount变量，它包含表中的记录数
            qDebug() << QString("%1 table has %2 loved records").arg(tableName).arg(recordCount);
        }
        qDebug()<<"recordCount=" << recordCount;
        return recordCount;

    }
}
// 删除数据
bool deleteData(QString &tableName, int musicId, int listIndex)
{
   QSqlQuery sqlQuery;
   QString createSql = QString("DELETE FROM %1 WHERE MusicID = %2 or listIndex = %3").arg(tableName).arg(musicId).arg(listIndex);
   bool bSuccess = sqlQuery.exec(createSql);
   qDebug()<< createSql << bSuccess <<__FUNCTION__<<__LINE__;
   if(!bSuccess)
   {
       qDebug()<<sqlQuery.lastError();
       return false;
   }
   else
   {
       qDebug()<<"deleted data success!";
       return true;
   }
}
int main(int argc, char *argv[])
{
    // 启动Qt应用程序，这通常在Qt主要应用程序对象中完成
    // QApplication app(argc, argv);

    // 添加SQLite数据库
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:"); // 使用内存数据库，也可以是文件路径

    // 打开数据库
    if (!db.open()) {
        qDebug() << "无法打开数据库";
        return -1;
    }
    QString tableName1 = "loveList";
    bool bsuccess = createListTable(tableName1);
    if (!bsuccess) {
        qDebug() << QString("创建表%1失败：%2" ).arg(tableName1);
        return -1;
    }
    // 执行SQL命令
    QSqlQuery query;
    bool success = query.exec("CREATE TABLE People (id INTEGER PRIMARY KEY, name TEXT )");
    if (!success) {
        qDebug() << "创建表失败：" << query.lastError();
        return -1;
    }

    success = query.exec("INSERT INTO People (name) VALUES ('John Doe')");
    if (!success) {
        qDebug() << "Insert Data failed ：" << query.lastError();
        return -1;
    }
    QString Sql = QString("INSERT INTO %1 (id, name) VALUES (:id, :name)").arg("People");
    query.prepare(Sql);
//    query.prepare("INSERT INTO People (id, name) "
//                  "VALUES (:id, :name)");
    query.bindValue(":id", 1001);
    query.bindValue(":name", "Bart");
    if (!query.exec()) {
        qDebug() << "Insert Data failed ：" << query.lastError();
        return -1;
    }

    QString tableName = "localList";
    QString createSql = QString("CREATE TABLE %1 (listIndex INTEGER ,MusicID INTEGER, MusicName TEXT, SingerName TEXT, album TEXT, songDuration FLOAT, MusicUrl TEXT PRIMARY KEY, MusicPic TEXT, bLoved INTEGER)").arg(tableName);
    query.prepare(createSql);
    if (!query.exec()) {
        qDebug() << "创建表2失败：" << query.lastError();
        return -1;
    }

   createSql = QString("INSERT INTO %1(listIndex,MusicID,MusicName,SingerName,album,songDuration,MusicUrl, MusicPic, bLoved) VALUES('0','1','QF','LIENZE','JD123','4.54','Https:www.baidu.com','qwww', '0') ").arg(tableName);
    if (!query.exec(createSql)) {
        qDebug() << "Insert Data failed 2：" << query.lastError();
        return -1;
    }

    createSql = QString("INSERT INTO %1(listIndex,MusicID,MusicName,SingerName,album,songDuration,MusicUrl, MusicPic, bLoved) VALUES(:listIndex, :MusicID,:MusicName,:SingerName,:album,:songDuration,:MusicUrl,:MusicPic,:bLoved) ").arg(tableName);
    query.prepare(createSql);
    query.bindValue(":listIndex", 1);
    query.bindValue(":MusicID",123456789);
    query.bindValue(":MusicName","QQI");
    query.bindValue(":SingerName","QQIxx");
    query.bindValue(":album","QQI JD");
    query.bindValue(":songDuration",  3.54);
    query.bindValue(":MusicUrl","http:www.baidu.com");
    query.bindValue(":MusicPic","QQI123");
    query.bindValue(":bLoved",0);
    if(!query.exec()){
        qDebug() << "Insert Data failed 3：" << query.lastError();
        return -1;
    }
    // 查询数据
    success = query.exec("SELECT * FROM People");
    if (!success) {
        qDebug() << "查询失败：" << query.lastError();
        return -1;
    }


    while (query.next()) {
        int id = query.value(0).toInt();
        QString name = query.value(1).toString();
        qDebug() << "ID:" << id << "Name:" << name;
    }

    success = query.exec(QString("SELECT * FROM %1").arg(tableName));
    if (!success) {
        qDebug() << "查询失败：" << query.lastError();
        return -1;
    }
    while (query.next()) {
        int index = query.value(0).toInt();
        int musicID = query.value(1).toInt();
        QString musicName = query.value(2).toString();
        QString singerName = query.value(3).toString();
        qDebug() <<"index:" << index << "musicID:" << musicID << "musicName:" << musicName
                <<"singerName:"<<singerName;
    }
    //创建一个空表格
    success = query.exec("CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT，score float)");
    if (!success) {
        qDebug() << "创建表失败：" << query.lastError();
        return -1;
    }
    //实现判断表格是否为空
    // 要检查的表名
    tableName = "student";
    //编写SQL查询语句
    createSql = QString("SELECT EXISTS(SELECT 1 FROM %1)").arg(tableName);
    //createSql = QString("SELECT EXISTS(SELECT 1 FROM localList)");  //查询表格中的第一项
    //createSql = QString("SELECT * FROM localList");

    //执行查询并获取结果
    success = query.exec(createSql);

    //判断表是否为空
     if (!success) {
        qDebug() <<QString("exeuce query failed.").arg(tableName);
        return -1;
     }
     while (query.next()) {
         int result = query.value(0).toInt();
         qDebug() << QString("%1 table EXISTS result:%2").arg(tableName).arg(result);

         if(result == 1){
            qDebug() << QString("%1  table is not empty").arg(tableName);
         }else{
            qDebug() << QString("%1  table is empty").arg(tableName);
         }

     }
    //tableName = "student";
     tableName = "localList";
     //获取数据表记录条数
     //QSqlQuery query;
     createSql = QString("SELECT COUNT (*) FROM %1").arg(tableName);
    //createSql = QString("SELECT COUNT (*) FROM localList");
     success = query.exec(createSql);
     if (!success) {
        QString dispinfo = QString("exeuce query failed.").arg(tableName);
        qDebug() << dispinfo;
        return -1;
     }
     if (query.next()) {
         QVariant count = query.value(0);
         // 如果count是一个整数，可以直接转换为int
         int recordCount = count.toInt();
         // 使用recordCount变量，它包含表中的记录数
         qDebug() << QString("%1 table has %2 recoeds").arg(tableName).arg(recordCount);
     }

     qDebug() << QString("%1 table recordnum is %2").arg(tableName).arg(getRcdCount(tableName));

     tableName = "recentList";
     createSql = QString("CREATE TABLE %1 (listIndex INTEGER PRIMARY KEY,MusicID INTEGER, MusicName TEXT, SingerName TEXT, album TEXT, songDuration FLOAT, MusicUrl TEXT, MusicPic TEXT, bLoved INTEGER)").arg(tableName);
     query.prepare(createSql);
     if (!query.exec()) {
         qDebug() << "创建表3失败：" << query.lastError();
         return -1;
     }

    createSql = QString("INSERT INTO %1(listIndex,MusicID,MusicName,SingerName,album,songDuration,MusicUrl, MusicPic,bLoved) VALUES('0','1','QF','LIENZE','JD123','4.54','Https:www.baidu.com','qwww',false) ").arg(tableName);
     if (!query.exec(createSql)) {
         qDebug() << "Insert Data failed 2：" << query.lastError();
         return -1;
     }

    tableName = "localList";
     qDebug()<<QString("table %1 has %2 records.").arg(tableName).arg(getRcdCount(tableName));
     MusicInfo* pMusicInfo = nullptr;
     for(int rcdno = 0; rcdno < 2; rcdno++){
         pMusicInfo = queryTableByindex(tableName,rcdno);
         if (pMusicInfo == nullptr) {
            qDebug() <<  QString("exeuce query failed.").arg(tableName);
            return -1;
         }
         qDebug()<<"pMusicInfo=" << pMusicInfo;
     }
//     pMusicInfo->setBLoved(1);
//     success = UpdateRowData(tableName, pMusicInfo,1);
//     if (!success) {
//        qDebug() << QString("Table %1 UpdateData failed.").arg(tableName);
//        //return -1;
//     }else{
//         pMusicInfo = queryTableByindex(tableName,1);
//         if (pMusicInfo == nullptr) {
//            qDebug() <<  QString("exeuce query failed.").arg(tableName);
//            return -1;
//         }
//         qDebug() << QString("Table %1 UpdateData success.").arg(tableName);
//     }
//     success = UpdateData(tableName, pMusicInfo);
//     if (!success) {
//        qDebug() << QString("Table %1 UpdateData failed.").arg(tableName);
//        return -1;
//     }else{
//         pMusicInfo = queryTableByindex(tableName,1);
//         if (pMusicInfo == nullptr) {
//            qDebug() <<  QString("exeuce query failed.").arg(tableName);
//            return -1;
//         }
//         qDebug() << QString("Table %1 UpdateData success.").arg(tableName);
//     }

     success = UpdateIntData(tableName, "bLoved",true,1);
     if (!success) {
        qDebug() << QString("Table %1 UpdateIntData failed.").arg(tableName);
        //return -1;
     }else{
         pMusicInfo = queryTableByindex(tableName,1);
         if (pMusicInfo == nullptr) {
            qDebug() <<  QString("exeuce query failed.").arg(tableName);
            return -1;
         }
         qDebug() << QString("Table %1 UpdateIntData success.").arg(tableName);
     }

     success = getlovedRcdCount(tableName);
     if (!success) {
        qDebug() << QString("Table %1 getlovedRcdCount failed.").arg(tableName);
        //return -1;
     }else{
         qDebug() << QString("Table %1 has %2 loved records.").arg(tableName).arg(success);
     }

     success = deleteData(tableName,1,0);
     if (!success) {
        qDebug() << QString("Table %1 delete Data failed.").arg(tableName);
        //return -1;
     }else{

         qDebug() << QString(" Table %1 has %2 records now.").arg(tableName).arg(getRcdCount(tableName));
         MusicInfo* p_Info = queryTableByindex(tableName,1);
         if(p_Info != nullptr){
             qDebug() << p_Info->getStrMusicName() ;
         }
     }


    // 关闭数据库
    db.close();

    // 返回成功代码
    return 0;
}
